import java.sql.*;
import java.util.ArrayList;

public class Database {
	/*
	 * Table definition : create table word_index(sno int auto_increment,word
	 * varchar(100),frequency int,primary key(sno));
	 */

	/*
	 * Table Definition: create table word_links(sno int,link
	 * varchar(100),foreign key(sno) references word_index(sno) on delete
	 * cascade);
	 */
	static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static String DB_URL = "jdbc:mysql://localhost:3306/";
	static String USERNAME = "root";
	static String PASSWORD = "root";
	static String DATABASE_NAME = "Google";
	static Connection connection = null;
	static Statement statement = null;
	static String word_index_table = "create table word_index(sno int auto_increment,word varchar(100),primary key(word),unique(sno));";
	static String word_link_table = "create table word_links(sno int,link varchar(100),frequency int,primary key(sno,link),foreign key(sno) references word_index(sno) on delete cascade);";
	static ResultSet resultSet;

	/* word_index table */
	static String word_index_table_name = "word_index";
	static String word_index_table_name_sno = "sno";
	static String word_index_table_name_word = "word";

	/* word_links table */
	static String word_link_table_name = "word_links";
	static String word_link_table_name_sno = "sno";
	static String word_link_table_name_link = "link";
	static String word_link_table_name_frequency = "frequency";

	public static void createdatabase() {
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager
					.getConnection(DB_URL, USERNAME, PASSWORD);
			statement = connection.createStatement();
			statement.executeUpdate("create database " + DATABASE_NAME);
			connection.close();
			statement.close();
		} catch (Exception exception) {

		}
	}

	public static void createTables() {
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL + DATABASE_NAME,
					USERNAME, PASSWORD);
			statement = connection.createStatement();
			statement.executeUpdate(word_index_table);
			statement.executeUpdate(word_link_table);
			connection.close();
			statement.close();
		} catch (Exception exception) {
		}
	}

	public static void insertIntoDatabase(String columns, String tableName,
			String values) {
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL + DATABASE_NAME,
					USERNAME, PASSWORD);
			statement = connection.createStatement();

			statement.execute("insert into " + tableName + "(" + columns + ") "
					+ " values(" + values + ");");
			connection.close();
			statement.close();
		} catch (Exception exception) {
//			exception.printStackTrace();
		}
	}

	public static String retrieveFromDatabase(String columnName,
			String tableName, String whereClause) {
		String result;
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL + DATABASE_NAME,
					USERNAME, PASSWORD);
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select " + columnName
					+ " from " + tableName + " where " + whereClause + ";");
			if (resultSet.first()) {
				result = resultSet.getString(1);
				connection.close();
				statement.close();
				return result;
			} else {
				connection.close();
				statement.close();
				return null;
			}

		} catch (Exception exception) {
//			exception.printStackTrace();
		}
		return null;
	}

	public static ArrayList<String> retrieveAllFromDatabase(String columnName,
			String tableName, String whereClause) {
		ArrayList<String> arrOfResults = new ArrayList<String>();
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL + DATABASE_NAME,
					USERNAME, PASSWORD);
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select " + columnName
					+ " from " + tableName + " where " + whereClause + ";");
			resultSet.first();
			if (resultSet.getString(1) == null) {
				connection.close();
				statement.close();
				return null;
			}

			arrOfResults.add(resultSet.getString(1));
			/* Doing a loop to get all the records */
			while (resultSet.next())
				arrOfResults.add(resultSet.getString(1));
			connection.close();
			statement.close();
			return arrOfResults;
		} catch (Exception exception) {
//			exception.printStackTrace();
		}
		return null;
	}

	public static boolean deleteFromDatabase(String tableName,
			String whereClause, String columnName) {
		boolean var = false;
		try {
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL + DATABASE_NAME,
					USERNAME, PASSWORD);
			statement = connection.createStatement();
			/* Statement for delete */
			statement.execute("delete from " + tableName + " where "
					+ whereClause);
			/* Statement for checking if deleted */
			if (retrieveFromDatabase(columnName, tableName, whereClause) == null)
				var = true;
			connection.close();
			statement.close();
			return var;
		} catch (Exception exception) {
		}
		return var;
	}

	public static String word_index_columns() {
		return word_index_table_name_word;
	}

	public static String word_links_columns() {
		return word_link_table_name_sno + "," + word_link_table_name_link + ","
				+ word_link_table_name_frequency;
	}

}
